import pymssql
import config

server = config.url
user = config.user
password = config.password
database = config.database
file = config.file
tablePreffix_index = config.tablePreffix_index
tablePreffix_name = file[tablePreffix_index].replace("-", "_")

try:

    # 打开数据库连接（ip/数据库用户名/登录密码/数据库名）
    db = pymssql.connect(server=server, user=user, password=password, database=database)
    print("数据库连接成功")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
except pymssql.Error as e:
    print("数据库连接失败：" + str(e))

# 读取数据库中的教室信息
def init_classroom():
    classroom_list = []
    try:
        cursor.execute(f"select id, capacity from {tablePreffix_name}_Room")
        result = cursor.fetchall()
        for row in result:
            classroom_list.append(row)
    except Exception as e:
        print(e)
    return classroom_list

# 读取数据库中教室距离的信息
def init_classroomDistance():
    classroomDistance_list = []
    try:
        cursor.execute(f"select room_id, to_room_id, value from {tablePreffix_name}_Room_Travel_Times")
        result = cursor.fetchall()
        for row in result:
            classroomDistance_list.append(row)
    except Exception as e:
        print(e)
    return classroomDistance_list

# 读取教室不可用时间段
def init_classroom_unavailable_time():
    classroom_unavailable_time_list = []
    try:
        cursor.execute(f"select room_id, days, start, length,weeks from {tablePreffix_name}_Room_UnAvailability")
        result = cursor.fetchall()
        for row in result:
            room_found = False
            for room in classroom_unavailable_time_list:
                if room[0] == row[0]:
                    room[1].append((row[1],row[2],row[3],row[4]))
                    room_found = True
                    break
            if room_found == False:
                classroom_unavailable_time_list.append([row[0], [(row[1],row[2],row[3],row[4])]])
    except Exception as e:
        print(e)
    return classroom_unavailable_time_list

# 读取学生选课表
def init_courses_tudents():
    course_students_list = []
    try:
        cursor.execute(f"select c_id, Sno from {tablePreffix_name}_StudentCourse")
        result = cursor.fetchall()
        for row in result:
            course_found = False
            for course in course_students_list:
                if course[0] == row[0]:
                    course[1].append(row[1])
                    course_found = True
                    break
            if course_found == False:
                course_students_list.append([row[0], [row[1]]])
    except Exception as e:
        print(e)
    return course_students_list

# 初始化课程教学任务
def init_task():
    task_list = []
    try:
        cursor.execute(f"select id, cid, class_id, limit, is_room from {tablePreffix_name}_ScoreManage")
        result = cursor.fetchall()
        for row in result:
            task_list.append(row)
    except Exception as e:
        print(e)
    return task_list

# 初始化教学任务和教室惩罚表
def init_task_room():
    task_room_list = []
    try:
        cursor.execute(f"select class_id, rid, penalty from {tablePreffix_name}_ScoreManageRPenalty")
        result = cursor.fetchall()
        for row in result:
            task_found = False
            for task in task_room_list:
                if task[0] == row[0]:
                    task[1].append((row[1],row[2]))
                    task_found = True
                    break
            if task_found == False:
                task_room_list.append([row[0], [(row[1],row[2])]])
    except Exception as e:
        print(e)
    return task_room_list

def init_task_time():
    task_time_list = []
    try:
        cursor.execute(f"select class_id, days, start, length,weeks, penalty from {tablePreffix_name}_ScoreManageTPenalty")
        result = cursor.fetchall()
        for row in result:
            task_found = False
            for task in task_time_list:
                if task[0] == row[0]:
                    task[1].append((row[1],row[2],row[3],row[4],row[5]))
                    task_found = True
                    break
            if task_found == False:
                task_time_list.append([row[0], [(row[1],row[2],row[3],row[4],row[5])]])
    except Exception as e:
        print(e)
    return task_time_list

# 初始化约束惩罚值列表
def init_constraint_penalty():
    constraint_penalty_list = []
    try:
        cursor.execute(f"select type, classids, penalty from {tablePreffix_name}_DistributionPenalty")
        result = cursor.fetchall()
        for row in result:
            constraint_penalty_list.append(row)
    except Exception as e:
        print(e)
    return constraint_penalty_list

# 初始化约束必须满足的列表
def init_constraint_required():
    constraint_required_list = []
    try:
        cursor.execute(f"select type, classids from {tablePreffix_name}_DistributionRequired")
        result = cursor.fetchall()
        for row in result:
            if row[1] != '':
                constraint_required_list.append(row)
    except Exception as e:
        print(e)
    return constraint_required_list